package org.springonrails.db;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Properties;
import java.util.Set;
import java.util.TreeSet;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springonrails.RailsException;
import org.springonrails.model.Attribute;
import org.springonrails.model.Entity;

public class DBEntityReverseMapper 
{
    private static Log log = LogFactory.getLog(DBEntityReverseMapper.class);
	
    //jdbc:mysql://[host][,failoverhost...][:port]/[database]
	public static final String JDBC_DRIVER_MYSQL = "com.mysql.jdbc.Driver";
    
	//jdbc:oracle:thin:@machineName:port:SID
    public static final String JDBC_DRIVER_ORACLE = "oracle.jdbc.driver.OracleDriver";
    
    //"jdbc:db2:// host_name:port_number/database_name"
    public static final String JDBC_DRIVER_DB2 = "COM.ibm.db2.jdbc.net.DB2Driver";
   
    //jdbc:sybase:Tds:host:port/databaseName com.sybase.jdbc3.jdbc.SybDriver
    public static final String JDBC_DRIVER_SYBASE = "com.sybase.jdbc2.jdbc.SybDriver";
    
    //jdbc:sqlserver://localhost;databaseName=AdventureWorks;integratedSecurity=true;applicationName=MyApp
    public static final String JDBC_DRIVER_MSSQL = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
	
    
    public static final String JDBC_PROP_DRIVER = "jdbc.driverClassName";
    public static final String JDBC_PROP_URL = "jdbc.url";
    public static final String JDBC_PROP_USER = "jdbc.username";
    public static final String JDBC_PROP_PASSWORD = "jdbc.password";
    public static final String JDBC_PROP_TABLES = "jdbc.tables.csv";
    public static final String JDBC_PROP_DBTYPE = "dbType";
    
    
    private List<Entity> entities = new ArrayList<Entity>();
    private Properties properties = null;
    
    public DBEntityReverseMapper(Properties properties)
    {
    	this.properties = properties;
    	if(properties.getProperty(JDBC_PROP_URL)!=null){
    		doReversMapping();
    	}
    	
    }
    
    
    
    public List<Entity> getEntities() {
		return entities;
	}




	protected void doReversMapping()
    {
    	log.info("Start reverse mapping......");
    	String tableCsv = properties.getProperty(JDBC_PROP_TABLES);
    	List<String> tables = null;
    	if(tableCsv!=null){
    		tables = Arrays.asList(tableCsv.split(","));
    	}
    	BasicDataSource datasource = initialDataSource();
    	Connection conn = null;
    	try{    	
    		conn = datasource.getConnection();
    		DatabaseMetaData dbmd = conn.getMetaData();
    		if(tables==null){
    			tables = new ArrayList<String>();    			
    			ResultSet tbrs = dbmd.getTables(null, null, "%", new String[]{"TABLE"});
    			while(tbrs.next())
    			{    				
    				tables.add(tbrs.getString(3));
    			}
    		}
    		
    		for (String tableName : tables) 
    		{
				Entity entity = new Entity();
				entity.setTableName(tableName);
				entity.setJavaName(toJavaName(tableName, true));
				
    			String sql = "SELECT * FROM " + tableName + " WHERE 1>2";
				Statement stmt = conn.createStatement();				
				ResultSet keys = dbmd.getPrimaryKeys(null, null, tableName);
				
				Set<String> keySets = new TreeSet<String>();
				while(keys.next())
				{
					keySets.add(keys.getString(4));
				}				
				log.info("Table :" + tableName + " Columns");
				ResultSet rs = stmt.executeQuery(sql);
				ResultSetMetaData rsmd = rs.getMetaData();
    			int colCount = rsmd.getColumnCount();
    			for(int i=1; i<=colCount; i++)
    			{
    				String colName = rsmd.getColumnName(i);
    				String typeName = rsmd.getColumnTypeName(i);
    				Attribute att = new Attribute();
    				att.setColumnName(colName);
    				att.setSqlType(typeName);
    				att.setJavaName(toJavaName(colName, false));  
    				att.setJavaType(getJavaTypeName( rsmd.getColumnType(i)));
    				if(keySets.contains(colName)) 
    				{
    					att.setPrimaryKey(true);
    					att.setAllowNull(false);
    				}
    				//log.info(typeName + "  " +);
    				
    				entity.addAttribute(att);
    			}    	
				entities.add(entity);
			}    	
    	}catch(Exception ex){
    		log.error("Failed to run reverse mapping", ex);
    		throw new RailsException(ex);
    	}finally{
    		if(conn!=null)try{conn.close();}catch(Exception e){;}    		
    		if(datasource!=null)try{datasource.close();}catch(Exception e){;}
    	}
    	log.info("End of reverse mapping");    	
    }
    
    
    protected String getJavaTypeName(int type)
    {
    	String ret = null;
    	
    	switch(type)
    	{
    	case Types.BINARY:
    	case Types.BLOB:
    	case Types.LONGVARBINARY:
    	case Types.VARBINARY:
    		ret = "byte[]";
    		break;
    	case Types.BIT:
    	case Types.BOOLEAN:
    		ret = "boolean";
    		break;
    	case Types.CHAR:
    	case Types.CLOB:
    	//case Types.LONGNVARCHAR:
    	case Types.LONGVARCHAR:   
    	//case Types.NVARCHAR:
    	case Types.VARCHAR:
    	//case Types.NCHAR:	
    	//case Types.NCLOB:
    		ret = "String";
    		break;
    	case Types.DATE:
    	case Types.TIME:
    	case Types.TIMESTAMP:
    		ret = "Date";
    		break;    		
    	case Types.NUMERIC:
    	case Types.DECIMAL:    		
    		ret = "java.math.BigDecimal";
    		break;
    	case Types.REAL:    	
    	case Types.DOUBLE:
    	case Types.FLOAT:
    		ret = "double";
    		break;
    	case Types.INTEGER:
    	case Types.SMALLINT:
    	case Types.TINYINT:    	
    		ret = "int";
    		break;
    	case Types.BIGINT:
    		ret = "long";
    		break;    	
    	case Types.ARRAY:
    	default:
    		ret = "Object";
    	    break;
    	}
    	return ret;
    }
    
    public String toJavaName(String value, boolean capFirst)
    {
    	String[] parts = value.split("_");
    	StringBuffer buf = new StringBuffer();
    	for(int i=0; i<parts.length; i++){
    		if(i==0 ){
    			if(capFirst){
    				buf.append(Character.toUpperCase(parts[i].charAt(0)));
    			}else{
    				buf.append(Character.toLowerCase(parts[i].charAt(0)));
    			}
    			buf.append(parts[i].substring(1).toLowerCase());
    		}else{
    			buf.append(Character.toUpperCase(parts[i].charAt(0)));
    			buf.append(parts[i].substring(1).toLowerCase());
    		}
    	}    	
    	return buf.toString();
    }
    
    protected BasicDataSource initialDataSource()
    {
    	String driver = properties.getProperty(JDBC_PROP_DRIVER);
    	String dbType = properties.getProperty(JDBC_PROP_DBTYPE);
    	if (driver==null && dbType!=null)
    	{
    		if(dbType.equalsIgnoreCase("oracle")){
    			driver = JDBC_DRIVER_ORACLE;
    		}else if(dbType.equalsIgnoreCase("mysql")){
    			driver = JDBC_DRIVER_MYSQL;
    		}else if(dbType.equalsIgnoreCase("sybase")){
    			driver = JDBC_DRIVER_SYBASE;
    		}else if(dbType.equalsIgnoreCase("mssql")){
    			driver = JDBC_DRIVER_MSSQL;
    		}else if(dbType.equalsIgnoreCase("db2")){
    			driver = JDBC_DRIVER_DB2;
    		}
    	}
    	if(driver==null){
    		throw new RailsException("Unrecognized dbType for reverse mapping.");
    	}
    	
    	String url = properties.getProperty(JDBC_PROP_URL);
    	String user = properties.getProperty(JDBC_PROP_USER);
    	String password = properties.getProperty(JDBC_PROP_PASSWORD);
    	BasicDataSource datasource = new BasicDataSource();
    	datasource.setDriverClassName(driver);
    	datasource.setUrl(url);
    	datasource.setUsername(user);
    	datasource.setPassword(password);    	
    	return datasource; 
    }
    

}
